// dbConnect.cs
// 
// Copyright (C) 2008 David M Burke
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.
//

using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace VolunteerTracker
{
	/// <summary>
	/// Handels DB connections.
	/// </summary>
	public class dbConnect
	{
		
		/// <summary>
		/// Gets the connection to the database
		/// </summary>
		public static MySqlConnection GetConnection()
		{
			MySqlConnection con = new MySqlConnection("server=localhost; user id=eastends_user; password=obama08; database=eastends_VolTrackDev; pooling=false;");
			return con;
		}
		
		/// <summary>
		/// Determine if a user has adminitrative priveledges.
		/// </summary>
		/// <param name="userId">
		/// email address of user to check
		/// A <see cref="System.String"/>
		/// </param>
		public static bool isAdmin(string userId)
		{
			MySqlConnection con = dbConnect.GetConnection();
			con.Open();
			string select = "select * from users where email = \'" + userId + "\';";
			MySqlCommand com = new MySqlCommand(select, con);
			MySqlDataReader dr = com.ExecuteReader();
			
			//If it returns anything we know it was successful
			if(dr.HasRows)
			{
				dr.Read();
				if(dr["admin"].ToString() == "True")
				{
					return true;
				}
			}
			return false;
		}
		
		/// <summary>
		/// Gets a datatable list of names from the teams table.  Includes the number (teamId) and team name (teamName).
		/// </summary>
		public static DataTable GetTeams()
		{
			MySqlConnection con = dbConnect.GetConnection();
			string select = "select * from teams;";
			DataTable dt = new DataTable();
			MySqlDataAdapter da = new MySqlDataAdapter(select, con);
			da.Fill(dt);
			return dt;
		}
		
		/// <summary>
		/// Find an event ID based on it's secondary key, name and eventDate
		/// If the event doesn't exist, add it.
		/// </summary>
		public static string LookUpEvent(string sname, string seventDate)
		{
			MySqlConnection con = dbConnect.GetConnection();
			con.Open();
			string select = "select * from events where name = ?name and eventDate = ?eventDate;";
			MySqlParameter name = new MySqlParameter("?name", MySqlDbType.VarChar);
			name.Value = sname;
			MySqlParameter eventDate = new MySqlParameter("?eventDate", MySqlDbType.DateTime);
			eventDate.Value = seventDate;
			MySqlCommand com = new MySqlCommand(select, con);
			com.Parameters.Add(name);
			com.Parameters.Add(eventDate);
			MySqlDataReader dr = com.ExecuteReader();
			dr.Read();
			
			return dr["eventId"].ToString();
			
			//TODO If the event doesn't exist.  Create it.
		}
		
		/// <summary>
		/// Look up userId using secondary key email and fname
		/// </summary>
		public static string LookUserId(string semail)
		{
			MySqlConnection con = dbConnect.GetConnection();
			con.Open();
			string select = "select * from events where email = ?email;";
			MySqlParameter email = new MySqlParameter("?email", MySqlDbType.VarChar);
			email.Value = semail;
			MySqlCommand com = new MySqlCommand(select, con);
			com.Parameters.Add(email);
			MySqlDataReader dr = com.ExecuteReader();
			dr.Read();
			
			return dr["userId"].ToString();
		}
		
		/// <summary>
		/// Link a event to a user by Id's
		/// </summary>
		public static void addEventToUser(string seventId, string suserId)
		{
			MySqlConnection con = dbConnect.GetConnection();
			con.Open();
			string insert = "insert into eventSignUp (eventId, UserId) values (?eventId, ?UserId)";
			MySqlParameter eventId = new MySqlParameter("?eventId", MySqlDbType.Int32);
			eventId.Value = seventId;
			MySqlParameter userId = new MySqlParameter("?userId", MySqlDbType.Int32);
			userId.Value = suserId;
			MySqlCommand com = new MySqlCommand(insert, con);
			com.Parameters.Add(eventId);
			com.Parameters.Add(userId);
			
		}
	}
}
